﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace HotelManagement.DAO
{
    class DataProvider
    {
        protected string connectionString;
        protected SqlConnection connection;
        protected string _error;
        public DataProvider()
        {
            connectionString = @"Data Source=PRMHUITEDU-PC\SQLEXPRESS;Initial Catalog=QuanLyKS;Integrated Security=True";
            this.connection = new SqlConnection(connectionString);
            
        }

        public bool Connect()
        {
            try
            {
                connection.Open();
            }
            catch (System.Exception e)
            {
                _error = e.ToString();
                System.Windows.Forms.MessageBox.Show(_error);
                return false;
            }
            return true;
        }

        public void Disconnect()
        {
            connection.Close();
        }
        #region "Create_a_ID"
        private string strProcedure = "";
        private string strPrefix = "";
        private int numPadLeft = 0;
        private void GetProcedure(string table)
        {
            switch (table)
            {
                case "bill":
                    numPadLeft = 4;
                    strProcedure = "select RIGHT(BillID," + numPadLeft + ") from " + table;
                    strPrefix = "BI";
                    break;
                case "billdetail":
                    numPadLeft = 7;
                    strProcedure = "select RIGHT(BillDetailID," + numPadLeft + ") from " + table;
                    strPrefix = "BID";
                    break;
                case "customer":
                    numPadLeft = 4;
                    strProcedure = "select RIGHT(CustomerID," + numPadLeft + ") from " + table;
                    strPrefix = "CU";
                    break;
                case "customertype":
                    numPadLeft = 2;
                    strProcedure = "select RIGHT(CusTypeID," + numPadLeft + ") from " + table;
                    strPrefix = "CT";
                    break;
                case "rent":
                    numPadLeft = 4;
                    strProcedure = "select RIGHT(RentID," + numPadLeft + ") from " + table;
                    strPrefix = "RE";
                    break;
                case "rentdetail":
                    numPadLeft = 7;
                    strProcedure = "select RIGHT(RentDetailID," + numPadLeft + ") from " + table;
                    strPrefix = "RED";
                    break;
                case "room":
                    numPadLeft = 3;
                    strProcedure = "select RIGHT(RoomID," + numPadLeft + ") from " + table;
                    strPrefix = "R";
                    break;
                case "roomtype":
                    numPadLeft = 2;
                    strProcedure = "select RIGHT(RoomTypeID," + numPadLeft + ") from " + table;
                    strPrefix = "RT";
                    break;
                case "service":
                    numPadLeft = 2;
                    strProcedure = "select RIGHT(ServiceID," + numPadLeft + ") from " + table;
                    strPrefix = "SEV";
                    break;
                case "report":
                    numPadLeft = 3;
                    strProcedure = "select RIGHT(ReportID," + numPadLeft + ") from " + table;
                    strPrefix = "REP";
                    break;
            }
        }

        protected String CreateCustomerID(String table)
        {
            GetProcedure(table);
            if (this.connection.State == ConnectionState.Closed)
            {
                Connect();
            }
            int[] ar = new int[100];
            SqlDataReader loginReader;
            SqlCommand cmd = new SqlCommand(strProcedure, connection);

            loginReader = cmd.ExecuteReader();
            int i = 0;
            while (loginReader.Read())
            {
                ar[i] = int.Parse(loginReader[0].ToString());
                i++;
            }
            Disconnect();

            int[] array = new int[i];
            for (int j = 0; j < i; j++)
            {
                array[j] = ar[j];
            }

            for (int m = 0; m < i - 1; m++)
            {
                for (int n = i + 1; n < i; n++)
                {
                    if (array[m] > array[n])
                    {
                        int tepm = array[m];
                        array[m] = array[n];
                        array[n] = tepm;
                    }
                }
            }
            int k = 0;
            while (k < i && array[k] == (k + 1))
            {
                k++;
            }
            return strPrefix + (k + 1).ToString().PadLeft(numPadLeft, '0');
        }

        #endregion

#region "Execute_Query"
        //Insert query
        protected bool _ExecuteQuery(string pStrQuery)
        {
            if (connection.State == System.Data.ConnectionState.Closed)
            {
                if (!Connect())
                {
                    return false;
                }
            }
            //return base._ExecuteQuery(strInsert);
            SqlCommand command = new SqlCommand(pStrQuery, this.connection);
            int kq = 0;
            try
            {
                kq = command.ExecuteNonQuery();
            }
            catch (SqlException el)
            {
                System.Windows.Forms.MessageBox.Show(el.ToString());
            }
            if (kq <= 0)
            {
                return false;
            }
            return true;
        }
        //Select data and pour into a datatable
        public DataTable _Select(string pStrSelect)
        {
            if (connection.State == System.Data.ConnectionState.Closed)
            {
                if (!Connect())
                {
                    return null;
                }
            }
            SqlDataAdapter cmd = new SqlDataAdapter(pStrSelect, this.connection);
            DataTable table = new DataTable();
            try
            {
                if (cmd.Fill(table) <= 0)
                {
                    return null;
                }
                
            }
            catch (System.Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);
            }
            return table;
        }

        protected int SelectLastInsertID(string table,string columnID)
        {
            string sql = "select max("+table+"."+columnID + ")from " + table;
            if (connection.State == System.Data.ConnectionState.Closed)
            {
                if (!Connect())
                {
                    return 0;
                }
            }
            DataTable tbl = this._Select(sql);
            DataRow r = tbl.Rows[0];
            return int.Parse(r[0].ToString());
        }
#endregion



    }
        
}
